# 导入模块
import requests
# 导入lxml模块解析HTML
from lxml import etree
# 导入PyMySQL模块用于跟MySQL交互
import pymysql.cursors

connection = pymysql.connect(host='192.168.44.128',
                             user='root',
                             password='123456',
                             database='spiders',
                             # MySQL驱动
                             cursorclass=pymysql.cursors.DictCursor)

douban_movie_base_url = 'https://movie.douban.com/top250?start={}&filter='
headers = {
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36"
}
# 将数据写入MySQL中
# 使用with建立连接可以在用完时自动关闭
with connection:
    # 建立连接并创建游标cursor
    # 游标cursor：用于执行SQL以及获取结果
    with connection.cursor() as cursor:
        """ MySQL的建表语句
        DROP TABLE IF EXISTS `movie_info`;
        CREATE TABLE `movie_info` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` varchar(255) DEFAULT NULL COMMENT '电影名称',
          `rating_nums` double DEFAULT NULL COMMENT '影片评分',
          `quote` varchar(500) DEFAULT NULL COMMENT '影片引言',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
        """

        insert_sql = 'insert into movie_info (name,rating_nums,quote) values (%s,%s,%s)'
        for i in range(1, 11):
            douban_movie_url = douban_movie_base_url.format((i - 1) * 25)
            # 使用requests模块通过get方式发起请求
            response = requests.get(url=douban_movie_url, headers=headers)
            print("当前正在获取：", douban_movie_url)
            # # 获取返回的文本类数据
            # print(response.text)
            # 使用xpath解析html页面
            # 参考地址：https://zhuanlan.zhihu.com/p/29436838
            xpath_obj = etree.HTML(response.text, etree.HTMLParser())
            # 搜索整个HTML页面，找到其中属性值为grid_view的ol标签下的所有li标签
            for li in xpath_obj.xpath("//ol[@class='grid_view']/li"):
                # 解析每一部影片的信息
                item = li.xpath("./div[@class='item']/div[@class='info']")[0]
                title = item.xpath("./div[1]/a/span[1]/text()")[0]
                rating = item.xpath(".//span[@class='rating_num']/text()")[0]
                quote_list = item.xpath(".//p[@class='quote']/span/text()")
                quote = ""
                if len(quote_list) == 1:
                    quote = quote_list[0]
                # print(title, rating, quote)
                # f.write(title + "," + rating + "," + quote + "\n")
                # 写入MySQL
                cursor.execute(insert_sql, (title, rating, quote))
            # 提交事务保证写入的数据能够在数据库中生效
            connection.commit()
